<?php

namespace TuxDrink\TuxDb\Mysql\Drink;

use TuxDrink\TuxDb\Drink\Util\AlcoholCalc;
use TuxDrink\TuxDb\Mysql\Drink\Model\AlcoholSumTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkBrandTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkCompanyTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkFlavorTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkFlavorTypeTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkIngredientTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkIngredientTypeTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkInventoryTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\DrinkTypeTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\GlobalImageLinkPendingTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\GlobalImageLinkTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\GlobalImageTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\LiquidColorTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\LiquidColorTypeTable;
use TuxDrink\TuxDb\Mysql\Drink\Model\PendingDrinkCommitsTable;
use TuxDrink\TuxDb\Mysql\TuxDbMysqlAbstract;
use TuxDrink\TuxDb\TuxDrinkInterface;
use Zend\Db\Sql\Expression;

class TuxDbMysqlDrink extends TuxDbMysqlAbstract implements TuxDrinkInterface {


	/**
	 * @var \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkTable
	 */
	protected $drinkTable;
	/**
	 *
	 * @var DrinkTypeTable
	 */
	protected $drinkTypeTable;
	/**
	 *
	 * @var DrinkCompanyTable
	 */
	protected $drinkCompanyTable;
	/**
	 *
	 * @var DrinkFlavorTypeTable
	 */
	protected $drinkFlavorTypeTable;
	/**
	 *
	 * @var LiquidColorTable
	 */
	protected $liquidColorTable;
	/**
	 *
	 * @var LiquidColorTypeTable
	 */
	protected $liquidColorTypeTable;
	/**
	 *
	 * @var DrinkFlavorTable
	 */
	protected $drinkFlavorTable;
	/**
	 *
	 * @var AlcoholSumTable
	 */
	protected $alcoholSumTable;
	/**
	 *
	 * @var DrinkIngredientTable
	 */
	protected $drinkIngredientTable;
	/**
	 *
	 * @var DrinkIngredientTypeTable
	 */
	protected $drinkIngredientTypeTable;
	/**
	 *
	 * @var DrinkBrandTable
	 */
	protected $drinkBrandTable;
	/**
	 *
	 * @var PendingDrinkCommitsTable
	 */

	protected $pendingDrinkCommitsTable;
	/**
	 *
	 * @var GlobalImageTable
	 */
	protected $globalImageTable;
	/**
	 *
	 * @var GlobalImageLinkTable
	 */
	protected $globalImageLinkTable;
	/**
	 *
	 * @var DrinkInventoryTable
	 */
	protected $drinkInventoryTable;
	/**
	 *
	 * @var GlobalImageLinkPendingTable
	 */
	protected $globalImageLinkPendingTable;
	protected $supportedImageMimeTypes = array('image/gif', 'image/jpeg', 'image/png', 'image/bmp', 'image/tiff');
	/**
	 *
	 * @var TuxDbMysqlImage
	 */
	private $tuxDbMysqlImage;
	private $config;
	
	/**
	 *
	 * @param TuxDbMysqlImage $tuxDbMysqlImage
	 */
	public function __construct($config, $tuxDbMysqlImage)
	{
		parent::__construct($config);
		$this->config = $config;
		$this->tuxDbMysqlImage = $tuxDbMysqlImage;
	}
	
	/**
	 * (non-PHPdoc)
	 * @see \TuxDb\TuxDrinkInterface::getLatestDrinks()
	 */
	public function getLatestDrinks($limit = null)
	{

		$theLimit = ($limit == null ? 5 : $limit);
		$select = $this->getDrinkFlavorTable()->newSelect();
		$this->getDrinkFlavorTable()->getSelect($select, array('drink_flavor_id'))
			->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id', array('Flavor' => 'drink_flavor_type_name'))
			->join('drink', 'drink.drink_id=drink_flavor.drink_id', array())
			->join('drink_brand', 'drink_brand.drink_brand_id = drink.drink_brand_id', array('Brand' => 'drink_brand_name'))
			->join('global_image_link', new Expression('global_image_link.table_pkey=drink_flavor.drink_flavor_id AND global_image_link.global_image_type_id=1'), array(), 'left')
			->join('global_image', 'global_image.global_image_id=global_image_link.global_image_id', array('Image' => 'global_image_id'), 'left')
			->join('drink_type', 'drink.drink_type_id = drink_type.drink_type_id', array('Type' => 'drink_type_name'))
			->join('drink_company', 'drink_company.drink_company_id = drink_brand.drink_company_id', array('Company' => 'drink_company_name'))
			->join('alcohol_sum', 'alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id', array('Vol' => 'alcohol_by_volume'))
			->group('drink_flavor.drink_flavor_id')->order('drink_flavor.drink_flavor_id desc')->limit(intval($theLimit));
		$resultSet = $this->getDrinkFlavorTable()->selectWith($select);
		$result = null;
		if ($resultSet->count() > 0) {
			$rows = $resultSet->toArray();
			$result = $rows; //TODO: check if bug still occures in latest zf2 version
		}
		return $result;


	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkFlavorTable
	 */
	private function getDrinkFlavorTable()
	{
		if (!$this->drinkFlavorTable) {
			$this->drinkFlavorTable = new DrinkFlavorTable($this->tuxDb->getInstance());
		}
		return $this->drinkFlavorTable;
	}

	public function getAllDrinks()
	{
		$select = $this->getDrinkFlavorTable()->newSelect();
		$this->getDrinkFlavorTable()->getSelect($select, array('id' => 'drink_flavor_id'))
			->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id', array('Flavor' => 'drink_flavor_type_name'))
			->join('drink', 'drink.drink_id=drink_flavor.drink_id', array())
			->join('drink_brand', 'drink_brand.drink_brand_id = drink.drink_brand_id', array('Brand' => 'drink_brand_name'))
			->join('global_image_link', new Expression('global_image_link.table_pkey=drink_flavor.drink_flavor_id AND global_image_link.global_image_type_id=1'), array(), 'left')
			->join('global_image', 'global_image.global_image_id=global_image_link.global_image_id', array('Image' => 'global_image_id'), 'left')
			->join('drink_type', 'drink.drink_type_id = drink_type.drink_type_id', array('Type' => 'drink_type_name'))
			->join('drink_company', 'drink_company.drink_company_id = drink_brand.drink_company_id', array('Company' => 'drink_company_name'))
			/*	->join('drink_ingredient','drink_ingredient.drink_flavor_id = drink_flavor.drink_flavor_id',array(),'left')
             ->join('drink_ingredient_type','drink_ingredient_type.drink_ingredient_type_id = drink_ingredient.drink_ingredient_type_id',array('drink_ingredients'=>new \Zend\Db\Sql\Expression('GROUP_CONCAT(drink_ingredient_type_name)')),'left')
            ->join('liquid_color','liquid_color.liquid_color_id = drink_flavor.liquid_color_id',array('is_transparent'))
            ->join('liquid_color_type','liquid_color_type.liquid_color_type_id = liquid_color.liquid_color_type_id',array('liquid_color_type_name'))
            */
			->join('alcohol_sum', 'alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id', array('Vol' => 'alcohol_by_volume'))
			->group('drink_flavor.drink_flavor_id')->order('drink_flavor.drink_flavor_id desc');/*->where
		 ->equalTo('drink_flavor_type.drink_flavor_type_name',$drinkFlavor)
		->equalTo('drink_company.drink_company_name',$drinkCompany)
		->equalTo('drink_brand.drink_brand_name',$drinkBrand)
		->equalTo('drink_type.drink_type_name',$drinkType)*/;
		//		$sql = $this->getDrinkFlavorTable()->getSql()->getSqlStringForSqlObject($select);
		//   die($sql);
		$resultSet = $this->getDrinkFlavorTable()->selectWith($select);
		$result = "";
		if ($resultSet->count() > 0) {
			$rows = $resultSet->toArray();
			if (count($rows) > 1) {
				$result = json_encode($rows);
			}
		}
		return $result;

	}

	public function updateDrinkFlavorData($drinkFlavorId, $data)
	{
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$drinkCompanyId = $this->getDrinkCompanyTable()->createIfNotExist(array('drink_company_name' => $data['drink_company']));
		$drinkBrandId = $this->getDrinkBrandTable()->createIfNotExist(array('drink_brand_name' => $data['drink_brand'], 'drink_company_id' => $drinkCompanyId));
		$drinkTypeId = $this->getDrinkTypeTable()->createIfNotExist(array('drink_type_name' => $data['drink_type']));
		$drinkFlavorTypeId = $this->getDrinkFlavorTypeTable()->createIfNotExist(array('drink_flavor_type_name' => $data['drink_flavor_type']));
		$liquidColorTypeId = $this->getLiquidColorTypeTable()->createIfNotExist(array('liquid_color_type_name' => $data['liquid_color_type']));
		$liquidColorId = $this->getLiquidColorTable()->createIfNotExist(array('liquid_color_type_id' => $liquidColorTypeId, 'is_transparent' => $data['liquid_is_transparent']));
		$drinkId = $this->getDrinkTable()->createIfNotExist(array(array('drink_type_id' => $drinkTypeId, 'drink_brand_id' => $drinkBrandId)));

		$alcoholArr = AlcoholCalc::calcByVolume($data['alcohol_vol']);
		$alcoholSumId = $this->getAlcoholSumTable()->createIfNotExist(array('alcohol_by_volume' => $alcoholArr['alcohol_by_volume']), array('alcohol_by_proof_us' => $alcoholArr['alcohol_by_proof_us'], 'alcohol_by_proof_uk' => $alcoholArr['alcohol_by_proof_uk']));
		//TODO: creationtimestamp is null
		$drinkFlavorTable->updateDrinkFlavor($drinkFlavorId, $drinkId, $drinkFlavorTypeId, $liquidColorId, $data['calories_for_100g'], $alcoholSumId, null);
	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkCompanyTable
	 */
	private function getDrinkCompanyTable() {
		if (!$this->drinkCompanyTable) {
			$this->drinkCompanyTable = new DrinkCompanyTable($this->tuxDb->getInstance());
		}
		return $this->drinkCompanyTable;
	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkBrandTable
	 */
	private function getDrinkBrandTable()
	{
		if (!$this->drinkBrandTable) {
			$this->drinkBrandTable = new DrinkBrandTable($this->tuxDb->getInstance());
		}
		return $this->drinkBrandTable;
	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkTypeTable
	 */
	private function getDrinkTypeTable()
	{
		if (!$this->drinkTypeTable) {
			$this->drinkTypeTable = new DrinkTypeTable($this->tuxDb->getInstance());
		}
		return $this->drinkTypeTable;
	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkFlavorTypeTable
	 */
	private function getDrinkFlavorTypeTable()
	{
		if (!$this->drinkFlavorTypeTable) {
			$this->drinkFlavorTypeTable = new DrinkFlavorTypeTable($this->tuxDb->getInstance());
		}
		return $this->drinkFlavorTypeTable;
	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\LiquidColorTypeTable
	 */
	private function getLiquidColorTypeTable()
	{
		if (!$this->liquidColorTypeTable) {
			$this->liquidColorTypeTable = new LiquidColorTypeTable($this->tuxDb->getInstance());
		}
		return $this->liquidColorTypeTable;
	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\LiquidColorTable
	 */
	private function getLiquidColorTable()
	{
		if (!$this->liquidColorTable) {
			$this->liquidColorTable = new LiquidColorTable($this->tuxDb->getInstance());
		}
		return $this->liquidColorTable;
	}
	
	/**
	 *
	 * @return DrinkTable
	 */
	private function getDrinkTable()
	{
		if (!$this->drinkTable) {
			$this->drinkTable = new DrinkTable($this->tuxDb->getInstance());
		}
		return $this->drinkTable;
	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\AlcoholSumTable
	 */
	private function getAlcoholSumTable()
	{
		if (!$this->alcoholSumTable) {
			$this->alcoholSumTable = new AlcoholSumTable($this->tuxDb->getInstance());
		}
		return $this->alcoholSumTable;
	}

	public function addDinkFlavorToUserInventoryIfNotExist($userId, $drinkFlavorId)
	{
		$drinkInventoryTable = $this->getDrinkInventoryTable();
		$drinkInventoryTable->createIfNotExist(array('user_id' => $userId, 'drink_flavor_id' => $drinkFlavorId));
	}
	
	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkInventoryTable
	 */
	public function getDrinkInventoryTable()
	{
		if (!$this->drinkInventoryTable) {
			$this->drinkInventoryTable = new DrinkInventoryTable($this->tuxDb->getInstance());
		}
		return $this->drinkInventoryTable;
	}

	public function getDrinkFlavorId($drinkType, $drinkBrand, $drinkFlavor, $drinkCompany)
	{
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select, array('drink_flavor_id'))
			->join('drink', 'drink.drink_id = drink_flavor.drink_id', array())
			->join('drink_type', 'drink.drink_type_id=drink_type.drink_type_id', array())
			->join('drink_brand', 'drink_brand.drink_brand_id=drink.drink_brand_id', array())
			->join('drink_company', 'drink_brand.drink_company_id = drink_company.drink_company_id', array())
			->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id = drink_flavor.drink_flavor_type_id', array())
			->where(array('drink_type.drink_type_name' => $drinkType, 'drink_flavor_type.drink_flavor_type_name' => $drinkFlavor,
				'drink_brand.drink_brand_name' => $drinkBrand, 'drink_company.drink_company_name' => $drinkCompany));
		$resultSet = $drinkFlavorTable->selectWith($select);
		if ($resultSet->count() == 0) {
			return null;
		}
		$retArray = $resultSet->toArray();
		return $retArray[0]['drink_flavor_id'];
	}

	public function getUserInventory($userId)
	{
		$drinkInventoryTable = $this->getDrinkInventoryTable();
		$select = $drinkInventoryTable->newSelect();
		$drinkInventoryTable->getSelect($select)
			->join('drink_flavor', 'drink_flavor.drink_flavor_id = drink_inventory.drink_flavor_id', array())
			->join('drink', 'drink.drink_id=drink_flavor.drink_id', array())
			->join('drink_brand', 'drink_brand.drink_brand_id = drink.drink_brand_id', array('Brand' => 'drink_brand_name'))
			->join('drink_company', 'drink_company.drink_company_id = drink_brand.drink_company_id', array('Company' => 'drink_company_name'))
			->join('drink_type', 'drink_type.drink_type_id = drink.drink_type_id', array('Type' => 'drink_type_name'))
			->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id = drink_flavor.drink_flavor_type_id', array('Flavor' => 'drink_flavor_type_name'))
			->join('alcohol_sum', 'drink_flavor.alcohol_sum_id = alcohol_sum.alcohol_sum_id', array('Vol' => 'alcohol_by_volume'))
			->join('global_image_link', new Expression('global_image_link.global_image_type_id = 1 and global_image_link.table_pkey = drink_flavor.drink_flavor_id'), array('Image' => 'global_image_id'), 'left')
			->where(array('drink_inventory.user_id' => $userId));
		$resultSet = $drinkInventoryTable->selectWith($select);
		if ($resultSet->count() > 0) {
			$rows = $resultSet->toArray();
			$result = json_encode($rows);
			return $result;
		}
		return null;
	}

	public function getDrinksQuickQuery($drinkStr = null, $limit = 100)
	{
		$select = $this->getDrinkFlavorTable()->newSelect();
		$this->getDrinkFlavorTable()->getSelect($select)
		->join('drink_flavor_type','drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id',array('Flavor'=>'drink_flavor_type_name'))
		->join('drink','drink.drink_id=drink_flavor.drink_id',array())
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('Brand'=>'drink_brand_name'))
		->join('global_image_link',new Expression('global_image_link.table_pkey=drink_flavor.drink_flavor_id AND global_image_link.global_image_type_id=1'),array(),'left')
			->join('global_image', 'global_image.global_image_id=global_image_link.global_image_id', array('Image' => 'global_image_id'), 'left')
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array('Type'=>'drink_type_name'))
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('Company'=>'drink_company_name'))
			->join('alcohol_sum', 'alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id', array('Vol' => 'alcohol_by_volume'))
			->group('drink_flavor.drink_flavor_id')->limit($limit)
			->where->OR->like('drink_brand.drink_brand_name', '%' . $drinkStr . '%')
			->OR->like('drink_company.drink_company_name', '%' . $drinkStr . '%')
			->OR->like('drink_type.drink_type_name', '%' . $drinkStr . '%');

		$resultSet=$this->getDrinkFlavorTable()->selectWith($select);
		$result = "";
		if ($resultSet->count()>0) {
			$rows=$resultSet->toArray();
			$result = json_encode($rows);
		}
		return $result;

	}

	public function findDrinkFlavorId($drinkCompany, $drinkBrand, $drinkFlavor)
	{
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select, array('drink_flavor_id'))
			->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id', array())
			->join('drink', 'drink.drink_id = drink_flavor.drink_id', array())
			->join('drink_brand', 'drink_brand.drink_brand_id=drink.drink_brand_id', array())
			->join('drink_company', 'drink_company.drink_company_id=drink_brand.drink_company_id', array())
			//    						->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array(''))
			->where(array('drink_brand.drink_brand_name' => $drinkBrand, 'drink_company.drink_company_name' => $drinkCompany, 'drink_flavor_type.drink_flavor_type_name' => $drinkFlavor));
		$resultSet = $drinkFlavorTable->selectWith($select);
		$resultArray = $resultSet->toArray();
		if (sizeof($resultArray) == 0) {
			return null;
		}
		$drinkFlavorId = $resultArray[0]['drink_flavor_id'];
		return $drinkFlavorId;
	}

	public function getDrinksQuery($drinkBrand = null, $drinkFlavor = null, $drinkCompany = null, $drinkType = null, $limit = 10)
	{


		$select = $this->getDrinkFlavorTable()->newSelect();
		$this->getDrinkFlavorTable()->getSelect($select)
		->join('drink_flavor_type','drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id',array('Flavor'=>'drink_flavor_type_name'))
		->join('drink','drink.drink_id=drink_flavor.drink_id',array())
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('Brand'=>'drink_brand_name'))
		->join('global_image_link',new Expression('global_image_link.table_pkey=drink_flavor.drink_flavor_id AND global_image_link.global_image_type_id=1'),array(),'left')
		->join('global_image','global_image.global_image_id=global_image_link.global_image_id',array('Image'=>'global_image_id'),'left')
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array('Type'=>'drink_type_name'))
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('Company'=>'drink_company_name'))
			/*->join('drink_ingredient','drink_ingredient.drink_flavor_id = drink_flavor.drink_flavor_id',array(),'left')
            ->join('drink_ingredient_type','drink_ingredient_type.drink_ingredient_type_id = drink_ingredient.drink_ingredient_type_id',array('drink_ingredients'=>new \Zend\Db\Sql\Expression('GROUP_CONCAT(drink_ingredient_type_name)')),'left')
            ->join('liquid_color','liquid_color.liquid_color_id = drink_flavor.liquid_color_id',array('is_transparent'))
            ->join('liquid_color_type','liquid_color_type.liquid_color_type_id = liquid_color.liquid_color_type_id',array('liquid_color_type_name'))
            */
			->join('alcohol_sum', 'alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id', array('Vol' => 'alcohol_by_volume'))
			->group('drink_flavor.drink_flavor_id')->limit($limit);

		if ($drinkBrand != null) {
			$select->where->like('drink_brand.drink_brand_name', '%' . $drinkBrand . '%');
		}

		if ($drinkFlavor != null) {
			$select->where->like('drink_flavor_type.drink_flavor_type_name', '%' . $drinkFlavor . '%');
		}

		if ($drinkCompany != null) {
			$select->where->like('drink_company.drink_company_name', '%' . $drinkCompany . '%');
		}

		if ($drinkType != null) {
			$select->where->like('drink_type.drink_type_name', '%' . $drinkType . '%');
		}

		$resultSet=$this->getDrinkFlavorTable()->selectWith($select);
		$result="";
		if ($resultSet->count()>0) {
			$rows=$resultSet->toArray();
			$result = json_encode($rows);
		}
		return $result;


	}

	/**
	 * (non-PHPdoc)
	 * @see \TuxDb\TuxDrinkInterface::autoCompleteDrinkCompany()
	 */
	public function autoCompleteDrinkCompany($partialDrinkCompany)
	{
		if ($partialDrinkCompany) {
			$select = $this->getDrinkCompanyTable()->newSelect();
			$this->getDrinkCompanyTable()->getSelect($select, array('drink_company_name'))->where->like('drink_company_name', '%' . $partialDrinkCompany . '%');
			$resultSet = $this->getDrinkCompanyTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[] = $row->drink_company_name;
			}
			return $resultArray;
		}
		return null;
	}

	public function autoCompleteDrinkBrand($partialDrinkBrand)
	{
		if ($partialDrinkBrand) {
			$select = $this->getDrinkBrandTable()->newSelect();
			$this->getDrinkBrandTable()->getSelect($select, array('drink_brand_name'))->where->like('drink_brand_name', '%' . $partialDrinkBrand . '%');
			$resultSet = $this->getDrinkBrandTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[] = $row->drink_brand_name;
			}
			return $resultArray;
		}

	}

	public function autoCompleteDrinkType($partialDrinkType)
	{
		if ($partialDrinkType) {
			$select = $this->getDrinkTypeTable()->newSelect();
			$this->getDrinkTypeTable()->getSelect($select, array('drink_type_name'))->where->like('drink_type_name', '%' . $partialDrinkType . '%');
			$resultSet = $this->getDrinkTypeTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[] = $row->drink_type_name;
			}
			return $resultArray;
		}

	}

	public function autoCompleteDrinkFlavorType($partialDrinkFlavor)
	{
		if ($partialDrinkFlavor) {
			$select = $this->getDrinkFlavorTypeTable()->newSelect();
			$this->getDrinkFlavorTypeTable()->getSelect($select, array('drink_flavor_type_name'))->where->like('drink_flavor_type_name', '%' . $partialDrinkFlavor . '%');
			$resultSet = $this->getDrinkFlavorTypeTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[] = $row->drink_flavor_type_name;
			}
			return $resultArray;
		}
		return null;

	}

	public function autoCompleteDrinkIngredient($partialDrinkIngredient)
	{
		if ($partialDrinkIngredient) {
			$select = $this->getDrinkIngredientTypeTable()->newSelect();
			$this->getDrinkIngredientTypeTable()->getSelect($select, array('drink_ingredient_type_name'))->where->like('drink_ingredient_type_name', '%' . $partialDrinkIngredient . '%');
			$resultSet = $this->getDrinkIngredientTypeTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[] = $row->drink_ingredient_type_name;
			}
			return $resultArray;
		}

	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkIngredientTypeTable
	 */
	private function getDrinkIngredientTypeTable()
	{
		if (!$this->drinkIngredientTypeTable) {
			$this->drinkIngredientTypeTable = new DrinkIngredientTypeTable($this->tuxDb->getInstance());
		}
		return $this->drinkIngredientTypeTable;
	}

	public function autoCompleteLiquidColorType($partialColorType)
	{
		if ($partialColorType) {
			$select = $this->getLiquidColorTypeTable()->newSelect();
			$this->getLiquidColorTypeTable()->getSelect($select, array('liquid_color_type_name'))->where->like('liquid_color_type_name', '%' . $partialColorType . '%');
			$resultSet = $this->getLiquidColorTypeTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[] = $row->liquid_color_type_name;
			}
			return $resultArray;

		}
	}

	public function addDrinkFlavorType($drinkFlavorTypeName)
	{
		$drinkFlavorTypeId = $this->getDrinkFlavorTypeTable()->createIfNotExist(array('drink_flavor_type_name' => $drinkFlavorTypeName));
		return $drinkFlavorTypeId;
	}

	public function addDrinkType($drinkTypeName)
	{
		$drinkTypeId = $this->getDrinkTypeTable()->createIfNotExist(array('drink_type_name' => $drinkTypeName));
		return $drinkTypeId;
	}

	public function addDrinkBrand($drinkBrandName, $drinkCompanyName)
	{
		$drinkCompanyId = $this->getDrinkCompanyTable()->createIfNotExist(array('drink_company_name' => $drinkCompanyName));
		$drinkBrandId = $this->getDrinkBrandTable()->createIfNotExist(array('drink_brand_name' => $drinkBrandName, 'drink_company_id' => $drinkCompanyId));
		return $drinkBrandId;
	}

	public function addDrinkCompany($drinkCompanyName, $drinkCompanyDesc)
	{
		$drinkCompanyId = $this->getDrinkCompanyTable()->createIfNotExist(array('drink_company_name' => $drinkCompanyName), array('drink_company_desc' => $drinkCompanyDesc));
		return $drinkCompanyId;
	}

	public function addLiquidColor($liquidColorTypeName, $liquidColorIsTransparent)
	{
		$liquidColorTypeId = $this->getLiquidColorTypeTable()->createIfNotExist(array('liquid_color_type_name' => $liquidColorTypeName));
		$liquidColorId = $this->getLiquidColorTable()->createIfNotExist(array('liquid_color_type_id' => $liquidColorTypeId, 'is_transparent' => $liquidColorIsTransparent));
		return $liquidColorId;
	}
	
	/*public function autoCompleteDrinkName($partialDrinkName) {
		if ($partialDrinkName) {
			$select = $this->getDrinkTable()->newSelect();
			$this->getDrinkTable()->getSelect($select,array('drink_name'))->where->like('drink_name','%'.$partialDrinkName.'%');
			$resultSet=$this->getDrinkTable()->selectWith($select);
			$resultArray = array();
			foreach ($resultSet as $row) {
				$resultArray[]=$row->drink_name;
			}
			return $resultArray;
		}
		
	}*/

	public function addLiquidColorType($liquidColorTypeName)
	{
		$liquidColorTypeId = $this->getLiquidColorTypeTable()->createIfNotExist(array('liquid_color_type_name' => $liquidColorTypeName));
		return $liquidColorTypeId;
	}

	public function addAlcoholVolume($alcoholByVolume, $alcoholByProofUs, $alcoholByProofUk)
	{
		$rowset = $this->getAlcoholSumTable()->select(array('alcohol_by_volume' => $alcoholByVolume));

		$row = $rowset->current();
		$alcoholSumId = null;

		if ($row != null) {
			$alcoholSumId = $row->alcohol_sum_id;
		} else {
			$data = AlcoholCalc::calcByVolume($alcoholByVolume);
			$affectedRows = $this->getAlcoholSumTable()->insert($data);
			if ($affectedRows != 1) {
				die("could not add row to alcohol sum table");
			}
			$alcoholSumId = $this->getAlcoholSumTable()->getLastInsertValue();
		}
		return $alcoholSumId;
	}

	public function findCompanyNameByBrand($drinkBrand)
	{
		$drinkCompany = "";
		if ($drinkBrand) {
			$select = $this->getDrinkBrandTable()->newSelect();
			$this->getDrinkBrandTable()
				->getSelect($select)
				->join('drink_company', 'drink_company.drink_company_id = drink_brand.drink_company_id', array('drink_company_name'))
				->where->equalTo('drink_brand_name', $drinkBrand);
			$rowset = $this->getDrinkBrandTable()->selectWith($select);
			$row = $rowset->current();
			if ($row) {
				$drinkCompany = $row->drink_company_name;
			}
		}
		return $drinkCompany;
	}

	/**
	 * @param $drinkBrand
	 * @param $drinkCompany
	 * @param $drinkType
	 * @return array
	 * @throws \Exception
	 */
	public function findDrinkFlavors($drinkBrand, $drinkCompany, $drinkType)
	{
		if (!$drinkBrand || !$drinkCompany || !$drinkType) {
			throw new \Exception("invalid parameters");
		}
		$select = $this->getDrinkFlavorTypeTable()->newSelect();
		$this->getDrinkFlavorTypeTable()->getSelect($select, array('drink_flavor_type_name'))
			->join('drink_flavor', 'drink_flavor.drink_flavor_type_id = drink_flavor_type.drink_flavor_type_id', array())
			->join('drink', 'drink.drink_id = drink_flavor.drink_id', array())
			->join('drink_brand', 'drink_brand.drink_brand_id = drink.drink_brand_id', array())
			->join('drink_company', 'drink_company.drink_company_id = drink_brand.drink_company_id', array())
			->join('drink_type', 'drink_type.drink_type_id = drink.drink_type_id', array())
			->where->equalTo('drink_brand_name', $drinkBrand)->equalTo('drink_company_name', $drinkCompany)->equalTo('drink_type_name', $drinkType);
		$resultSet = $this->getDrinkFlavorTypeTable()->selectWith($select);
		$resultArray = array();
		foreach ($resultSet as $row) {
			$resultArray[] = $row->drink_flavor_type_name;
		}
		return $resultArray;

	}

	public function addDrinkPending($drinkName, $drinkIngredients, $drinkCompany, $drinkBrand,
									$drinkType, $drinkFlavorType, $liquidColorType, $liquidColorIsTransparent,
									$caloriesFor100g, $alcoholVol)
	{
		$pendingDrinkCommitsId = $this->getPendingDrinkCommitsTable()->addPendingDrinkCommits(0, $drinkBrand, $drinkType, $drinkCompany, $drinkFlavorType, $liquidColorType, $liquidColorIsTransparent, $caloriesFor100g, $alcoholVol, $drinkIngredients);
		return $pendingDrinkCommitsId;
	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\PendingDrinkCommitsTable
	 */
	private function getPendingDrinkCommitsTable()
	{
		if (!$this->pendingDrinkCommitsTable) {
			$this->pendingDrinkCommitsTable = new PendingDrinkCommitsTable($this->tuxDb->getInstance());
		}
		return $this->pendingDrinkCommitsTable;
	}

	public function addDrink($drinkCompany, $drinkBrand,
							 $drinkType, $drinkIngredients, $drinkFlavorType, $liquidColorType, $liquidColorIsTransparent,
							 $caloriesFor100g, $alcoholVol, $pictureFile = null, $previewImageRotation = 0, $uploadToS3 = true, $jsonException = false)
	{
		$drinkCompanyId = $this->getDrinkCompanyTable()->createIfNotExist(array('drink_company_name' => $drinkCompany));
		$drinkBrandId = $this->getDrinkBrandTable()->createIfNotExist(array('drink_brand_name' => $drinkBrand, 'drink_company_id' => $drinkCompanyId));
		$drinkTypeId = $this->getDrinkTypeTable()->createIfNotExist(array('drink_type_name' => $drinkType));
		$drinkFlavorTypeId = $this->getDrinkFlavorTypeTable()->createIfNotExist(array('drink_flavor_type_name' => $drinkFlavorType));
		$liquidColorTypeId = $this->getLiquidColorTypeTable()->createIfNotExist(array('liquid_color_type_name' => $liquidColorType));
		$liquidColorId = $this->getLiquidColorTable()->createIfNotExist(array('liquid_color_type_id' => $liquidColorTypeId, 'is_transparent' => $liquidColorIsTransparent));
		$rowset = $this->getAlcoholSumTable()->select(array('alcohol_by_volume' => $alcoholVol));

		$row = $rowset->current();
		$alcoholSumId=null;

		if ($row != null) {
			$alcoholSumId=$row->alcohol_sum_id;
		} else {
			$data = AlcoholCalc::calcByVolume($alcoholVol);
			$affectedRows = $this->getAlcoholSumTable()->insert($data);
			if ($affectedRows != 1) {
				if ($jsonException) {
					return json_encode(array('message' => 'could not add row to alcohol sum table'));
				} else {
					die("could not add row to alcohol sum table");
				}
			}
			$alcoholSumId=$this->getAlcoholSumTable()->getLastInsertValue();
		}

		$drinkData = array(
			'drink_type_id' => $drinkTypeId,
			'drink_brand_id' => $drinkBrandId
		);
		$drinkId = $this->getDrinkTable()->createIfNotExist($drinkData);

		$drinkFlavorData = array(
			'drink_id' => $drinkId,
			'drink_flavor_type_id' => $drinkFlavorTypeId,
		);

		$drinkFlavorOptionalData = array(
			'liquid_color_id' => $liquidColorId,
			'calories_for_100g' => $caloriesFor100g,
			'alcohol_sum_id' => $alcoholSumId

		);
		$isRowCreated = null;
		$drinkFlavorId = $this->getDrinkFlavorTable()->createIfNotExist($drinkFlavorData, $drinkFlavorOptionalData, $isRowCreated);
		if (!$drinkFlavorId) {
			if ($jsonException) {
				return json_encode(array('message' => 'could not add row to drink flavor table'));
			}
			throw new \Exception("could not add row to drink flavor table");
		}

		if (!$isRowCreated) { // user is modifing drink flavor
			$pendingData = array(

				'pending_drink_commits_userid' => 0,
				'drink_brand' => $drinkBrand,
				'drink_type' => $drinkType,
				'company_name' => $drinkCompany,
				'drink_flavor_type' => $drinkFlavorType,
				'liquid_color' => $liquidColorType,
				'liquid_color_transparent' => $liquidColorIsTransparent,
				'calories_for_100g' => $caloriesFor100g,
				'alcohol_vol' => $alcoholVol,
				'drink_ingredients' => $drinkIngredients

			);
			$this->getPendingDrinkCommitsTable()->insert($pendingData);
		} else {
			foreach (explode(',', $drinkIngredients) as $ingredient) {
				$data = array('drink_ingredient_type_name' => $ingredient);
				$drinkIngredientTypeId = $this->getDrinkIngredientTypeTable()->createIfNotExist($data);

				$data = array('drink_ingredient_type_id' => $drinkIngredientTypeId, 'drink_flavor_id' => $drinkFlavorId);
				$affectedRows = $this->getDrinkIngredientTable()->insert($data);
				if ($affectedRows != 1) {
					die("could not add drink ingredient");
				}
			}
		}
		if ($pictureFile['error'] == 0) { // image uploaded properly

			if (in_array($pictureFile['type'], $this->supportedImageMimeTypes)) {
				$imageId = $this->tuxDbMysqlImage->uploadImage($pictureFile, $previewImageRotation, $uploadToS3);
				$pendingImageLinkData = array(

					'global_image_link_pending_imageid' => $imageId,
					'global_image_link_pending_typeid' => 1,
					'global_image_link_pending_pkey' => $drinkFlavorId,

				);
				$this->getGlobalImageLinkPendingTable()->insert($pendingImageLinkData);

			} else {
				if ($jsonException) {
					return json_encode(array('message' => 'information was added to db without the image, image format not supported', 'type' => 'warning'));
				} else {
					error_log('information was added to db without the image, image format not supported');
				}
				//$this->messanger->addMessage('information was added to db without the image, image format not supported');

			}
		} else if ($pictureFile['error'] == 1) {
			if ($jsonException) {
				return json_encode(array('message' => 'size of file is bigger then upload_max_filesize directive', 'type' => 'error'));
			} else {
				error_log("size of file is bigger then upload_max_filesize directive");
			}
		} else if ($pictureFile['error'] != 4) {
			if ($jsonException) {
				return json_encode(array('message' => 'there was an error uploading the image. info: ' . var_export($pictureFile, 1), 'type' => 'error'));
			} else {
				error_log("there was an error uploading the image. info: " . var_export($pictureFile, 1));
			}
			//$this->messanger->addMessage('information was added, but image upload failed!');
		} else {
			//$this->messanger->addMessage('information was added succesfully!');
		}

	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\DrinkIngredientTable
	 */
	private function getDrinkIngredientTable()
	{
		if (!$this->drinkIngredientTable) {
			$this->drinkIngredientTable = new DrinkIngredientTable($this->tuxDb->getInstance());
		}
		return $this->drinkIngredientTable;
	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\GlobalImageLinkPendingTable
	 */
	private function getGlobalImageLinkPendingTable()
	{
		if (!$this->globalImageLinkPendingTable) {
			$this->globalImageLinkPendingTable = new GlobalImageLinkPendingTable($this->tuxDb->getInstance());
		}
		return $this->globalImageLinkPendingTable;
	}
	
	public function queryDrinkFlavor($drinkFlavor,$drinkCompany,$drinkBrand,$drinkType) {
		if (!is_string($drinkFlavor) || !is_string($drinkCompany) || !is_string($drinkBrand) || !is_string($drinkType)) {
			die('missing parameters');
		}
		$select = $this->getDrinkTable()->newSelect();
		$this->getDrinkTable()->getSelect($select)
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array())
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array())
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array())
		->join('drink_flavor','drink_flavor.drink_id = drink.drink_id',array('calories_for_100g'))
		->join('drink_ingredient','drink_ingredient.drink_flavor_id = drink_flavor.drink_flavor_id',array())
		->join('drink_flavor_type','drink_flavor.drink_flavor_type_id = drink_flavor_type.drink_flavor_type_id',array('drink_flavor_type_name'))
		->join('drink_ingredient_type','drink_ingredient_type.drink_ingredient_type_id = drink_ingredient.drink_ingredient_type_id',array('drink_ingredients'=>new \Zend\Db\Sql\Expression('GROUP_CONCAT(drink_ingredient_type_name)')))
		->join('liquid_color','liquid_color.liquid_color_id = drink_flavor.liquid_color_id',array('is_transparent'))
		->join('liquid_color_type','liquid_color_type.liquid_color_type_id = liquid_color.liquid_color_type_id',array('liquid_color_type_name'))
		->join('alcohol_sum','alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id',array('alcohol_by_volume'))
		->join('global_image_link',new Expression('global_image_link.table_pkey = drink_flavor.drink_flavor_id and global_image_link.global_image_type_id = 1'),array('global_image_id'), 'left')
		->where
		->equalTo('drink_flavor_type.drink_flavor_type_name',$drinkFlavor)
		->equalTo('drink_company.drink_company_name',$drinkCompany)
		->equalTo('drink_brand.drink_brand_name',$drinkBrand)
		->equalTo('drink_type.drink_type_name',$drinkType);
		$resultSet=$this->getDrinkTable()->selectWith($select);
		$result="";
		if ($resultSet->count()>0) {
			$rows=$resultSet->toArray();
			if (count($rows) > 1 || $rows[0]['liquid_color_type_name'] != null ) { //overcome zf2 b7 bug http://framework.zend.com/issues/browse/ZF2-522
				$result=json_encode($rows[0]);
			}
		}
		return $result;
	}
	
	public function deleteUserInventory($userId) {
		$drinkInventoryTable = $this->getDrinkInventoryTable();
		$drinkInventoryTable->delete(array('user_id'=>$userId));
	}
	
	public function getFullDrinkInformation($drinkFlavorId) {
		$select = $this->getDrinkTable()->newSelect();
		$this->getDrinkTable()->getSelect($select)
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array('drink_type_name'))
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('drink_brand_name'))
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('drink_company_name'))
		->join('drink_flavor','drink_flavor.drink_id = drink.drink_id',array('calories_for_100g'))
		->join('drink_ingredient','drink_ingredient.drink_flavor_id = drink_flavor.drink_flavor_id',array())
		->join('drink_flavor_type','drink_flavor.drink_flavor_type_id = drink_flavor_type.drink_flavor_type_id',array('drink_flavor_type_name'))
		->join('drink_ingredient_type','drink_ingredient_type.drink_ingredient_type_id = drink_ingredient.drink_ingredient_type_id',array('drink_ingredients'=>new \Zend\Db\Sql\Expression('GROUP_CONCAT(drink_ingredient_type_name)')))
		->join('liquid_color','liquid_color.liquid_color_id = drink_flavor.liquid_color_id',array('is_transparent'))
		->join('liquid_color_type','liquid_color_type.liquid_color_type_id = liquid_color.liquid_color_type_id',array('liquid_color_type_name'))
		->join('alcohol_sum','alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id',array('alcohol_by_volume'))
		->join('global_image_link',new Expression('global_image_link.table_pkey = drink_flavor.drink_flavor_id and global_image_link.global_image_type_id = 1'),array('global_image_id'), 'left')
		->where
		->equalTo('drink_flavor.drink_flavor_id',$drinkFlavorId);
		$resultSet=$this->getDrinkTable()->selectWith($select);
		return $resultSet->toArray()[0];
	}
		
	public function getDrinkInformation($drinkFlavorId) {
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select,array('id'=>'drink_flavor_id'))
		->join('drink','drink.drink_id = drink_flavor.drink_id',array(),'left')
		->join('drink_flavor_type','drink_flavor_type.drink_flavor_type_id = drink_flavor.drink_flavor_type_id',array('Flavor'=>'drink_flavor_type_name'),'left')
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('Brand'=>'drink_brand_name'),'left')
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('Company'=>'drink_company_name'),'left')
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array('Type'=>'drink_type_name'))
		->join('global_image_link',new Expression('global_image_link.table_pkey = drink_flavor.drink_flavor_id and global_image_link.global_image_type_id=1'),array('Image'=>'global_image_id'),'left')

		->where(array('drink_flavor.drink_flavor_id'=>$drinkFlavorId));
		$resultSet = $drinkFlavorTable->selectWith($select);
		if ($resultSet->count() == 0) {
			return null;
		} else {
			return $resultSet->toArray()[0];
		}
	}
	
	public function getDrinkInformationJson($drinkFlavor,$drinkBrand,$drinkCompany) {
		$select = $this->getDrinkTable()->newSelect();
		$this->getDrinkTable()->getSelect($select)
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array('drink_type_name'))
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('drink_brand_name'))
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('drink_company_name'))
		->join('drink_flavor','drink_flavor.drink_id = drink.drink_id',array('calories_for_100g'))
		->join('drink_ingredient','drink_ingredient.drink_flavor_id = drink_flavor.drink_flavor_id',array())
		->join('drink_flavor_type','drink_flavor.drink_flavor_type_id = drink_flavor_type.drink_flavor_type_id',array('drink_flavor_type_name'))
		->join('drink_ingredient_type','drink_ingredient_type.drink_ingredient_type_id = drink_ingredient.drink_ingredient_type_id',array('drink_ingredients'=>new \Zend\Db\Sql\Expression('GROUP_CONCAT(drink_ingredient_type_name)')))
		->join('liquid_color','liquid_color.liquid_color_id = drink_flavor.liquid_color_id',array('is_transparent'))
		->join('liquid_color_type','liquid_color_type.liquid_color_type_id = liquid_color.liquid_color_type_id',array('liquid_color_type_name'))
		->join('alcohol_sum','alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id',array('alcohol_by_volume'))
		->join('global_image_link',new Expression('global_image_link.table_pkey = drink_flavor.drink_flavor_id and global_image_link.global_image_type_id = 1'),array('global_image_id'), 'left')
		->where
		->equalTo('drink_flavor_type.drink_flavor_type_name',$drinkFlavor)
		->equalTo('drink_brand.drink_brand_name',$drinkBrand)
		->equalTo('drink_company.drink_company_name',$drinkCompany);
		$resultSet=$this->getDrinkTable()->selectWith($select);
		return json_encode($resultSet->toArray()[0]);

	}

	public function getDrinkId($drinkFlavor,$drinkBrand,$drinkCompany) {

    }
	
	public function getDrinkInformationJsonById($drinkFlavorId) {
		$select = $this->getDrinkTable()->newSelect();
		$this->getDrinkTable()->getSelect($select)
		->join('drink_type','drink.drink_type_id = drink_type.drink_type_id',array('drink_type_name'))
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('drink_brand_name'))
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('drink_company_name'))
		->join('drink_flavor','drink_flavor.drink_id = drink.drink_id',array('calories_for_100g'))
		->join('drink_ingredient','drink_ingredient.drink_flavor_id = drink_flavor.drink_flavor_id',array())
		->join('drink_flavor_type','drink_flavor.drink_flavor_type_id = drink_flavor_type.drink_flavor_type_id',array('drink_flavor_type_name'))
		->join('drink_ingredient_type','drink_ingredient_type.drink_ingredient_type_id = drink_ingredient.drink_ingredient_type_id',array('drink_ingredients'=>new \Zend\Db\Sql\Expression('GROUP_CONCAT(drink_ingredient_type_name)')))
		->join('liquid_color','liquid_color.liquid_color_id = drink_flavor.liquid_color_id',array('is_transparent'))
		->join('liquid_color_type','liquid_color_type.liquid_color_type_id = liquid_color.liquid_color_type_id',array('liquid_color_type_name'))
		->join('alcohol_sum','alcohol_sum.alcohol_sum_id = drink_flavor.alcohol_sum_id',array('alcohol_by_volume'))
		->join('global_image_link',new Expression('global_image_link.table_pkey = drink_flavor.drink_flavor_id and global_image_link.global_image_type_id = 1'),array('global_image_id'), 'left')
		->where
		->equalTo('drink_flavor.drink_flavor_id',$drinkFlavorId);
		$resultSet=$this->getDrinkTable()->selectWith($select);
		return json_encode($resultSet->toArray()[0]);

	}

	public function udpateDrinkFlavor($drinkFlavorId, $drinkBrandName, $drinkFlavorTypeName, $drinkCompanyName)
	{
		if ($drinkBrandName != null) {
			$this->updateDrinkBrandName($drinkFlavorId, $drinkBrandName);
		}
		if ($drinkFlavorTypeName != null) {
			$this->updateDrinkFlavorTypeName($drinkFlavorId, $drinkFlavorTypeName);
		}
		if ($drinkCompanyName != null) {
			$this->updateDrinkCompany($drinkFlavorId, $drinkCompanyName);
		}
	}
	
	public function updateDrinkBrandName($drinkFlavorId,$drinkBrandName) {
		$drinkBrandTable = $this->getDrinkBrandTable();
		$drinkBrandId=$drinkBrandTable->createIfNotExist(array('drink_brand_name'=>$drinkBrandName));
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select,array('drink_id'))->where(array('drink_flavor_id'=>$drinkFlavorId));
		$resultSet = $drinkFlavorTable->selectWith($select);
		if ($resultSet->count() == 1) {
			$row = $resultSet->current();
			$drinkId=$row->drink_id;
			$drinkTable = $this->getDrinkTable();
			$select = $drinkTable->newSelect();
			$drinkTable->update(array('drink_brand_id'=>$drinkBrandId),array('drink_id'=>$drinkId));
		}

	}
	
	public function updateDrinkFlavorTypeName($drinkFlavorId, $drinkFlavorTypeName) {
		$drinkFlavorTypeTable = $this->getDrinkFlavorTypeTable();
		$drinkFlavorTypeId= $drinkFlavorTypeTable->createIfNotExist(array('drink_flavor_type_name'=>$drinkFlavorTypeName));
		$this->getDrinkFlavorTable()->update(array('drink_flavor_type_id'=>$drinkFlavorTypeId),array('drink_flavor_id'=>$drinkFlavorId));
	}
	
	public function updateDrinkCompany($drinkFlavorId,$drinkCompanyName) {
		$drinkCompanyTable = $this->getDrinkCompanyTable();
		$drinkCompanyId = $drinkCompanyTable->createIfNotExist(array('drink_company_name'=>$drinkCompanyName));
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select,array('drink_id'))->where(array('drink_flavor_id'=>$drinkFlavorId));
		$resultSet = $drinkFlavorTable->selectWith($select);
		if ($resultSet->count() == 1) {
			$row = $resultSet->current();
			$drinkId = $row->drink_id;
			$drinkTable = $this->getDrinkTable();
			$select = $drinkTable->newSelect();
			$drinkTable->getSelect($select,array('drink_brand_id'))->where(array('drink_id'=>$drinkId));
			$resultSet=$drinkTable->selectWith($select);
			if ($resultSet->count() == 1) {
				$row = $resultSet->current();
				$drinkBrandId = $row->drink_brand_id;
				$drinkBrandTable = $this->getDrinkBrandTable();
				$drinkBrandTable->update(array('drink_company_id'=>$drinkCompanyId),array('drink_brand_id'=>$drinkBrandId));
			}
		}
	}

	public function updateDrinkFlavorImageId($drinkFlavorId, $globalImageId)
	{
		$globalImageLinkTable = $this->getGlobalImageLinkTable();
		$globalImageLinkTable->delete(array('global_image_type_id' => 1, 'table_pkey' => $drinkFlavorId));
		$globalImageLinkTable->addGlobalImageLink($globalImageId, 1, $drinkFlavorId);
	}

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\GlobalImageLinkTable
	 */
	private function getGlobalImageLinkTable()
	{
		if (!$this->globalImageLinkTable) {
			$this->globalImageLinkTable = new GlobalImageLinkTable($this->tuxDb->getInstance());
		}
		return $this->globalImageLinkTable;
	}
	
	public function getFullDrinkName($drinkFlavorId) {
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select,array('full_drink_name'=>new Expression("concat(drink_company.drink_company_name,' - ',
														drink_brand.drink_brand_name, ' - ',
														drink_flavor_type.drink_flavor_type_name)")))
		->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id')
		->join('drink','drink.drink_id = drink_flavor.drink_id')
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id')
		->join('drink_company','drink_company.drink_company_id=drink_brand.drink_company_id')
		->where(array('drink_flavor.drink_flavor_id'=>$drinkFlavorId));
		$resultSet = $drinkFlavorTable->selectWith($select);
		if ($resultSet->count() != 1) {
			return null;
		}
		return $resultSet->toArray()[0]['full_drink_name'];
	}
	
	public function finDrinkNamesByPartial($drinkNamePartial) {;
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select,array('full_drink_name'=>new Expression('concat_ws(" - ",drink_brand_name,drink_flavor_type_name,drink_company_name)')))
		->join('drink_flavor_type', 'drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id')
		->join('drink','drink.drink_id = drink_flavor.drink_id')
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id')
		->join('drink_company','drink_company.drink_company_id=drink_brand.drink_company_id')
		->having->like('full_drink_name',"%".$drinkNamePartial."%");
		$resultSet = $drinkFlavorTable->selectWith($select);
		$result=array();
		foreach ($resultSet as $row) {
			array_push($result, $row->full_drink_name);
		}
		return $result;
			}

	public function finDrinkNGenericNamesByPartial($partialName) {
        $sql = "select concat_ws(\" - \",drink_brand_name,drink_flavor_type_name,drink_company_name) as full_drink_name
from drink_flavor
join drink_flavor_type on drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id
join drink on drink.drink_id = drink_flavor.drink_id
 join drink_brand on drink_brand.drink_brand_id = drink.drink_brand_id
join drink_company on drink_company.drink_company_id=drink_brand.drink_company_id
		having full_drink_name like :full_drink_name1
union
select concat('generic - ',drink_type.drink_type_name,' - ', drink_flavor_type.drink_flavor_type_name) as drink_generic_name from drink_generic
join drink_type on drink_type.drink_type_id = drink_generic.drink_type_id
join drink_flavor_type on drink_flavor_type.drink_flavor_type_id = drink_generic.drink_flavor_type_id
having drink_generic_name like :full_drink_name2";
        $statement=$this->getDrinkFlavorTable()->getAdapter()->query($sql);
        $resultSet = $statement->execute(array('full_drink_name1'=>'%'.$partialName.'%','full_drink_name2'=>'%'.$partialName.'%'));
        $resultArr = array();
        foreach ($resultSet as $row) {
            array_push($resultArr,$row['full_drink_name']);
        }
        return $resultArr;
    }

    public function checkIfDrinkExists($drinkBrand,$drinkCompany,$drinkFlavorType) {

        $drinkFlavorTable = $this->getDrinkFlavorTable();
        $select = $drinkFlavorTable->newSelect();
        $drinkFlavorTable->getSelect($select,array(new Expression('1')))
            ->join('drink_flavor_type','drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id',array())
            ->join('drink','drink.drink_id = drink_flavor.drink_id',array())
            ->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array())
            ->join('drink_company','drink_company.drink_company_id=drink_brand.drink_company_id',array())
            ->where->equalTo('drink_brand.drink_brand_name',$drinkBrand)
            ->equalTo('drink_company.drink_company_name',$drinkCompany)
            ->equalTo('drink_flavor_type.drink_flavor_type_name',$drinkFlavorType);
        $resultSet=$drinkFlavorTable->selectWith($select);
        return $resultSet->count() == 1;
    }

    public function findDrinkId($drinkBrand,$drinkCompany,$drinkFlavorType) {

        $drinkFlavorTable = $this->getDrinkFlavorTable();
        $select = $drinkFlavorTable->newSelect();
        $drinkFlavorTable->getSelect($select,array('drink_flavor_type_id'))
            ->join('drink_flavor_type','drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id',array())
            ->join('drink','drink.drink_id = drink_flavor.drink_id',array())
            ->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array())
            ->join('drink_company','drink_company.drink_company_id=drink_brand.drink_company_id',array())
            ->where->equalTo('drink_brand.drink_brand_name',$drinkBrand)
            ->equalTo('drink_company.drink_company_name',$drinkCompany)
            ->equalTo('drink_flavor_type.drink_flavor_type_name',$drinkFlavorType);
        $resultSet=$drinkFlavorTable->selectWith($select)->toArray();
        return isset($resultSet['0']) ? $resultSet[0]['drink_flavor_type_id'] : null;
    }

	/**
	 *
	 * @return \TuxDrink\TuxDb\Mysql\Drink\Model\GlobalImageTable
	 */
	private function getGlobalImageTable()
	{
		if (!$this->globalImageTable) {
			$this->globalImageTable = new GlobalImageTable($this->tuxDb->getInstance());
		}
		return $this->globalImageTable;
	}

}
